/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.yunhoo.deptBudget.bean;

import com.yunhoo.base.model.Department;
import com.yunhoo.deptBudget.model.BudgetStats;
import com.yunhoo.deptBudget.model.Pzxx;
import com.yunhoo.sys.db.util.IbatisTemplate;
import com.yunhoo.sys.util.common.CommonParam;
import com.yunhoo.sys.util.common.LoginParam;
import com.yunhoo.sys.util.common.Null;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.SessionScoped;
import javax.faces.context.FacesContext;
import javax.faces.model.SelectItem;
import javax.servlet.ServletContext;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFFooter;
import org.apache.poi.hssf.usermodel.HSSFHeader;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.CreationHelper;

/**
 * 部门预算报表
 *
 * @author Administrator
 */
@ManagedBean(name = "budgetStatsBean")
@SessionScoped
public class DeptBudgetReportBean {

    private List<SelectItem> yearli = null;
    private List<SelectItem> monthli = null;
    private Department currentDept = null;
    private Integer iyear = null;
    private Integer imonth = null;
    private Integer month_begin = null;//起始月份
    private Integer month_end = null;//结束月份
    private List<SelectItem> monthli_end = null;
    //
    private List<BudgetStats> deptStatsli = null;//当前部门 预算执行统计 
    private List<BudgetStats> allStatsli = null;//所有部门预算执行统计
    private List<BudgetStats> allStatsByFunli = null;//按 功能科目 分级 的 预算执行统计
    private List<BudgetStats> budgetStatsli = null;//预算统计
    private List<BudgetStats> pubCostStatsli = null;//公用经费支出 统计
    //
    private List<Pzxx> pzxxli = null;//凭证信息

    public DeptBudgetReportBean() {
        System.out.println("DeptBudgetReportBean init");
        initDate();
        System.out.println(" yearli : " + yearli);
    }

    private void initDate() {
        yearli = CommonParam.yearList();
        monthli = new ArrayList<SelectItem>();
        for (int i = 1; i < 13; i++) {
            monthli.add(new SelectItem(i, i + "月"));
        }
        monthli_end = new ArrayList<SelectItem>();
        for (int i = 12; i > 0; i--) {
            monthli_end.add(new SelectItem(i, i + "月"));
        }
    }

    public void queryAllli() {//所有部门预算统计数据
        Map map = new HashMap();
        map.put("iyear", iyear);
        map.put("imonth", imonth);
        map.put("orgCode", getOrgCode());
        //allStatsli = dao.queryList(map, "budgetStats");
        allStatsli = IbatisTemplate.queryForList("deptBudgetStats.queryListAllDeptli", map);
        System.out.println("allStatsli: " + allStatsli.size());
        if (!Null.check(allStatsli)) {
            allStatsli = formatli(allStatsli);
        }
    }

    public void queryDeptli() {//当前部门预算统计数据
        Map map = new HashMap();
        map.put("iyear", iyear);
        map.put("imonth", imonth);
        map.put("deptcode", getCurDeptCode());
        map.put("orgCode", getOrgCode());
        //deptStatsli = dao.queryList(map, "budgetStats");
        deptStatsli = IbatisTemplate.queryForList("deptBudgetStats.queryListAllDeptli", map);
        if (!Null.check(deptStatsli)) {
            deptStatsli = formatli(deptStatsli);
            deptStatsli.remove(0);
            deptStatsli.get(0).setFinProjName("合计");
        }
    }

    public void queryFunli() {//按 功能科目 分级 的 预算统计
        Map map = new HashMap();
        map.put("iyear", iyear);
        map.put("imonth", imonth);
        map.put("orgCode", getOrgCode());
        //allStatsByFunli = dao.queryList01(map, "budgetStats");
        allStatsByFunli = IbatisTemplate.queryForList("deptBudgetStats.queryListByFun", map);
        if (!Null.check(allStatsByFunli)) {
            allStatsByFunli = formatli1(allStatsByFunli);
        }
    }

    public void queryBudgetli() {
        Map map = new HashMap();
        map.put("iyear", iyear);
        map.put("orgCode", getOrgCode());
        //budgetStatsli = IbatisTemplate.queryList02(map, "budgetStats");
        budgetStatsli = IbatisTemplate.queryForList("deptBudgetStats.queryListBudget", map);
        if (!Null.check(budgetStatsli)) {
            budgetStatsli = formatli(budgetStatsli);
        }
    }

    public void showDetail_bgt(BudgetStats row) {
        if (row.getDeptCode() == null || row.getDeptCode().isEmpty() || imonth == null) {
            pzxxli = null;
            return;
        }
        String projCode = null;
        if (row.getProjCode() != null && !row.getProjCode().trim().isEmpty()) {
            projCode = "'" + row.getProjCode().trim() + "'";
        }
        Map map = new HashMap();
        map.put("bmdm", row.getDeptCode());
        map.put("kmdm", projCode);
        map.put("iyear", LoginParam.getIyear());
        map.put("imonth", imonth);
        map.put("orgCode",LoginParam.getOrg().getCode());
        pzxxli = IbatisTemplate.queryForList("deptBudgetStats.queryListPzxx", map);
    }


    public void showDetail_pubCost(BudgetStats row) {
        if (row.getDeptCode() == null || row.getDeptCode().isEmpty() || month_begin == null || month_end == null) {
            pzxxli = null;
            return;
        }
        String projCode = null;
        if (row.getProjCode() != null && !row.getProjCode().trim().isEmpty()) {
            projCode = "'" + row.getProjCode().trim() + "'";
        }
        Map map = new HashMap();
        map.put("bmdm", row.getDeptCode());
        map.put("kmdm", projCode);
        map.put("iyear", LoginParam.getIyear());
        map.put("month_begin", month_begin);
        map.put("month_end", month_end);
        map.put("orgCode",LoginParam.getOrg().getCode());
        pzxxli = IbatisTemplate.queryForList("deptBudgetStats.queryListPzxx", map);
    }

    public void queryPubCostli_all() {
        Map map = new HashMap();
        map.put("iyear", iyear);
        map.put("orgCode", getOrgCode());
        map.put("month_begin", month_begin);
        map.put("month_end", month_end);
        //budgetStatsli = IbatisTemplate.queryList02(map, "budgetStats");
        pubCostStatsli = IbatisTemplate.queryForList("deptBudgetStats.queryListDeptPubCost", map);
        if (!Null.check(pubCostStatsli)) {
            pubCostStatsli = formatli2(pubCostStatsli);
        }
    }

    public void queryPubCostli_dept() {
        String deptCode = LoginParam.getDept().getCode();
        if (deptCode == null || deptCode.isEmpty()) {
            return;
        }
        System.out.println("deptCode :  " + deptCode);
        Map map = new HashMap();
        map.put("iyear", iyear);
        map.put("orgCode", getOrgCode());
        map.put("month_begin", month_begin);
        map.put("month_end", month_end);
        map.put("deptCode", deptCode);
        //budgetStatsli = IbatisTemplate.queryList02(map, "budgetStats");
        pubCostStatsli = IbatisTemplate.queryForList("deptBudgetStats.queryListDeptPubCost", map);
        if (!Null.check(pubCostStatsli)) {
            pubCostStatsli = formatli2(pubCostStatsli);
            pubCostStatsli.remove(0);
            pubCostStatsli.get(0).setProjName("合计");
        }
    }

    private List<BudgetStats> formatli2(List<BudgetStats> datali) {
        for (BudgetStats budget : datali) {
            if (Null.check(budget.getDeptCode())) {//总计行
                budget.setDeptName("总计");
            }
            if (!Null.check(budget.getProjCode())) {
                budget.setDeptName("");
            }
        }
        return datali;
    }

    private List<BudgetStats> formatli(List<BudgetStats> datali) {
        for (BudgetStats budget : datali) {
            if (Null.check(budget.getDeptCode())) {//总计行
                budget.setDeptName("总计");
            }
            if (!Null.check(budget.getProjCode())) {
                budget.setFinProjName("");
            }
            if (!Null.check(budget.getFinProjCode())) {
                budget.setDeptName("");
            }
        }
        return datali;
    }

    private List<BudgetStats> formatli1(List<BudgetStats> datali) {
        for (BudgetStats budget : datali) {
            if (Null.check(budget.getLei())) {
                budget.setLei("合计");
            }
            if (!Null.check(budget.getKuan())) {
                budget.setLei("");
            }
            if (!Null.check(budget.getXiang())) {
                budget.setKuan("");
            }
            if (!Null.check(budget.getFinProjCode())) {
                budget.setXiang("");
            }
            if (!Null.check(budget.getProjCode())) {
                budget.setFinProjName("");
            }
        }
        return datali;
    }

    private String getCurDeptCode() {
        return LoginParam.getDept().getCode();
    }

    private String getOrgCode() {
        return LoginParam.getOrg().getCode();
    }

    public void exportExcel1() {
        if (pubCostStatsli != null && pubCostStatsli.size() > 0) {
            HSSFWorkbook demoWorkBook = new HSSFWorkbook();
            HSSFSheet demoSheet = demoWorkBook.createSheet("公用经费支出统计");
            String[] excelColumns = initTabeleHeader1();
            List<BudgetStats> rows = pubCostStatsli;

            HSSFHeader header = demoSheet.getHeader();
            header.setCenter("公用经费支出统计");
            HSSFRow headerRow = demoSheet.createRow(1);
            for (int i = 0; i < excelColumns.length; i++) {
                HSSFCell headerCell = headerRow.createCell((short) i);
                //headerCell.setEncoding(HSSFCell.ENCODING_UTF_16);
                headerCell.setCellValue(excelColumns[i]);
            }
            //填充数据
            int rowIndex = 2;
            for (BudgetStats row : rows) {
                createTableRow1(demoWorkBook, demoSheet, row, rowIndex, iyear);
                rowIndex++;
            }
            exportExcel(demoWorkBook, demoSheet);
        }
    }
    //初始化表头信息

    public String[] initTabeleHeader1() {
        String[] excelColumns = {"部门", "预算项目", "支出"};
        return excelColumns;
    }

    public static void createTableRow1(HSSFWorkbook demoWorkBook, HSSFSheet demoSheet, BudgetStats buds, int rowIndex, Integer iyear) {
        //创建第rowIndex行
        //HSSFWorkbook workbook = new HSSFWorkbook();
        CreationHelper createHelper = demoWorkBook.getCreationHelper();
        HSSFCellStyle dstyle = demoWorkBook.createCellStyle();
        dstyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
        HSSFCellStyle dstyled = demoWorkBook.createCellStyle();
        dstyled.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
        HSSFFont font = demoWorkBook.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 12);//设置字体大小
        HSSFCellStyle cellStyle = demoWorkBook.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
        cellStyle.setFont(font);//选择需要用到的字体格式

        HSSFCell cell1 = null;
        HSSFRow row1 = demoSheet.createRow(0);
        demoSheet.addMergedRegion(new Region((short) 0, (short) 0, (short) 0, (short) 6));
        cell1 = row1.createCell(0);
        cell1.setCellValue(iyear + "年度-公用经费支出统计");
        cell1.setCellStyle(cellStyle);

        HSSFCell cell = null;
        HSSFRow row = demoSheet.createRow(rowIndex);
        cell = row.createCell(0);
        cell.setCellValue(Null.convert(buds.getDeptName()));
        cell = row.createCell(1);
        cell.setCellValue(Null.convert(buds.getProjName()));
        cell = row.createCell(2);
        cell.setCellValue(Null.convert(buds.getCost()));
        cell.setCellStyle(dstyle);
    }

    public void exportExcelFor() {
        if (deptStatsli != null && deptStatsli.size() > 0) {
            HSSFWorkbook demoWorkBook = new HSSFWorkbook();
            HSSFSheet demoSheet = demoWorkBook.createSheet("部门财务预算执行统计");
            String[] excelColumns = initTabeleHeaderFor();
            List<BudgetStats> rows = deptStatsli;

            HSSFHeader header = demoSheet.getHeader();
            header.setCenter("部门财务预算执行统计");
            HSSFRow headerRow = demoSheet.createRow(1);
            for (int i = 0; i < excelColumns.length; i++) {
                HSSFCell headerCell = headerRow.createCell((short) i);
                //headerCell.setEncoding(HSSFCell.ENCODING_UTF_16);
                headerCell.setCellValue(excelColumns[i]);
            }
            //填充数据
            int rowIndex = 2;
            for (BudgetStats row : rows) {
                createTableRowFor(demoWorkBook, demoSheet, row, rowIndex, iyear);
                rowIndex++;
            }
            exportExcel(demoWorkBook, demoSheet);
        }
    }
    //初始化表头信息

    public String[] initTabeleHeaderFor() {
        String[] excelColumns = {"部门", "预算项目", "内容及工作量", "预算", "支出", "进度", "余额"};
        return excelColumns;
    }

    public static void createTableRowFor(HSSFWorkbook demoWorkBook, HSSFSheet demoSheet, BudgetStats buds, int rowIndex, Integer iyear) {
        //创建第rowIndex行
        //HSSFWorkbook workbook = new HSSFWorkbook();
        CreationHelper createHelper = demoWorkBook.getCreationHelper();
        HSSFCellStyle dstyle = demoWorkBook.createCellStyle();
        dstyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
        HSSFCellStyle dstyled = demoWorkBook.createCellStyle();
        dstyled.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
        HSSFFont font = demoWorkBook.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 12);//设置字体大小
        HSSFCellStyle cellStyle = demoWorkBook.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
        cellStyle.setFont(font);//选择需要用到的字体格式

        HSSFCell cell1 = null;
        HSSFRow row1 = demoSheet.createRow(0);
        demoSheet.addMergedRegion(new Region((short) 0, (short) 0, (short) 0, (short) 6));
        cell1 = row1.createCell(0);
        cell1.setCellValue(iyear + "年度-财务预算执行统计");
        cell1.setCellStyle(cellStyle);

        HSSFCell cell = null;
        HSSFRow row = demoSheet.createRow(rowIndex);
        cell = row.createCell(0);
        cell.setCellValue(Null.convert(buds.getDeptName()));
        cell = row.createCell(1);
        cell.setCellValue(Null.convert(buds.getFinProjName()));
        cell = row.createCell(2);
        cell.setCellValue(Null.convert(buds.getProjName()));
        cell = row.createCell(3);
        cell.setCellValue(Null.convert(buds.getBudget()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(4);
        cell.setCellValue(Null.convert(buds.getCost()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(5);
        if (!Null.check(buds.getBudget())) {
            cell.setCellValue(Null.convert(buds.getCost() / buds.getBudget()));
            cell.setCellStyle(dstyled);
        } else {
            cell.setCellValue("");
        }
        cell = row.createCell(6);
        cell.setCellValue(Null.convert(buds.getBudget() - buds.getCost()));
        cell.setCellStyle(dstyle);

    }

    public void exportExcel() {
        if (allStatsli != null && allStatsli.size() > 0) {
            HSSFWorkbook demoWorkBook = new HSSFWorkbook();
            HSSFSheet demoSheet = demoWorkBook.createSheet("各部门财务预算执行统计");
            String[] excelColumns = initTabeleHeader();
            List<BudgetStats> rows = allStatsli;

            HSSFHeader header = demoSheet.getHeader();
            header.setCenter("各部门财务预算执行统计");
            HSSFRow headerRow = demoSheet.createRow(1);
            for (int i = 0; i < excelColumns.length; i++) {
                HSSFCell headerCell = headerRow.createCell((short) i);
                //headerCell.setEncoding(HSSFCell.ENCODING_UTF_16);
                headerCell.setCellValue(excelColumns[i]);
            }
            //填充数据
            int rowIndex = 2;
            for (BudgetStats row : rows) {
                createTableRow(demoWorkBook, demoSheet, row, rowIndex, iyear);
                rowIndex++;
            }
            exportExcel(demoWorkBook, demoSheet);
        }
    }
    //初始化表头信息

    public String[] initTabeleHeader() {
        String[] excelColumns = {"部门", "预算项目", "内容及工作量", "预算", "支出", "进度", "余额"};
        return excelColumns;
    }

    public static void createTableRow(HSSFWorkbook demoWorkBook, HSSFSheet demoSheet, BudgetStats buds, int rowIndex, Integer iyear) {
        //创建第rowIndex行
        //HSSFWorkbook workbook = new HSSFWorkbook();
        CreationHelper createHelper = demoWorkBook.getCreationHelper();
        HSSFCellStyle dstyle = demoWorkBook.createCellStyle();
        dstyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
        HSSFCellStyle dstyled = demoWorkBook.createCellStyle();
        dstyled.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
        HSSFFont font = demoWorkBook.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 12);//设置字体大小
        HSSFCellStyle cellStyle = demoWorkBook.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
        cellStyle.setFont(font);//选择需要用到的字体格式

        HSSFCell cell1 = null;
        HSSFRow row1 = demoSheet.createRow(0);
        demoSheet.addMergedRegion(new Region((short) 0, (short) 0, (short) 0, (short) 6));
        cell1 = row1.createCell(0);
        cell1.setCellValue(iyear + "年度-财务预算执行统计");
        cell1.setCellStyle(cellStyle);

        HSSFCell cell = null;
        HSSFRow row = demoSheet.createRow(rowIndex);
        cell = row.createCell(0);
        cell.setCellValue(Null.convert(buds.getDeptName()));
        cell = row.createCell(1);
        cell.setCellValue(Null.convert(buds.getFinProjName()));
        cell = row.createCell(2);
        cell.setCellValue(Null.convert(buds.getProjName()));
        cell = row.createCell(3);
        cell.setCellValue(Null.convert(buds.getBudget()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(4);
        cell.setCellValue(Null.convert(buds.getCost()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(5);
        if (!Null.check(buds.getBudget())) {
            cell.setCellValue(Null.convert(buds.getCost() / buds.getBudget()));
            cell.setCellStyle(dstyled);
        } else {
            cell.setCellValue("");
        }
        cell = row.createCell(6);
        cell.setCellValue(Null.convert(buds.getBudget() - buds.getCost()));
        cell.setCellStyle(dstyle);

    }

    public void exportExcelFun() {
        if (allStatsByFunli != null && allStatsByFunli.size() > 0) {
            HSSFWorkbook demoWorkBook = new HSSFWorkbook();
            HSSFSheet demoSheet = demoWorkBook.createSheet("财务预算执行统计(按类目)");
            String[] excelColumns = initTabeleHeaderFun();
            List<BudgetStats> rows = allStatsByFunli;

            HSSFHeader header = demoSheet.getHeader();
            header.setCenter("财务预算执行统计(按类目)");
            HSSFRow headerRow = demoSheet.createRow(1);
            for (int i = 0; i < excelColumns.length; i++) {
                HSSFCell headerCell = headerRow.createCell((short) i);
                //headerCell.setEncoding(HSSFCell.ENCODING_UTF_16);
                headerCell.setCellValue(excelColumns[i]);
            }
            //填充数据
            int rowIndex = 2;
            for (BudgetStats row : rows) {
                createTableRowFun(demoWorkBook, demoSheet, row, rowIndex, iyear);
                rowIndex++;
            }
            exportExcel(demoWorkBook, demoSheet);
        }
    }
    //初始化表头信息

    public String[] initTabeleHeaderFun() {
        String[] excelColumns = {"类", "款", "项", "预算项目", "内容及工作量", "预算", "支出", "进度", "余额"};
        return excelColumns;
    }

    public static void createTableRowFun(HSSFWorkbook demoWorkBook, HSSFSheet demoSheet, BudgetStats buds, int rowIndex, Integer iyear) {
        //创建第rowIndex行
        //HSSFWorkbook workbook = new HSSFWorkbook();
        CreationHelper createHelper = demoWorkBook.getCreationHelper();
        HSSFCellStyle dstyle = demoWorkBook.createCellStyle();
        dstyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
        HSSFCellStyle dstyled = demoWorkBook.createCellStyle();
        dstyled.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
        HSSFFont font = demoWorkBook.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 12);//设置字体大小
        HSSFCellStyle cellStyle = demoWorkBook.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
        cellStyle.setFont(font);//选择需要用到的字体格式

        HSSFCell cell1 = null;
        HSSFRow row1 = demoSheet.createRow(0);
        demoSheet.addMergedRegion(new Region((short) 0, (short) 0, (short) 0, (short) 8));
        cell1 = row1.createCell(0);
        cell1.setCellValue(iyear + "年度-财务预算执行统计");
        cell1.setCellStyle(cellStyle);

        HSSFCell cell = null;
        HSSFRow row = demoSheet.createRow(rowIndex);
        cell = row.createCell(0);
        cell.setCellValue(Null.convert(buds.getLei()));
        cell = row.createCell(1);
        cell.setCellValue(Null.convert(buds.getKuan()));
        cell = row.createCell(2);
        cell.setCellValue(Null.convert(buds.getXiang()));
        cell = row.createCell(3);
        cell.setCellValue(Null.convert(buds.getFinProjName()));
        cell = row.createCell(4);
        cell.setCellValue(Null.convert(buds.getProjName()));
        cell = row.createCell(5);
        cell.setCellValue(Null.convert(buds.getBudget()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(6);
        cell.setCellValue(Null.convert(buds.getCost()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(7);
        if (!Null.check(buds.getBudget())) {
            cell.setCellValue(Null.convert(buds.getCost() / buds.getBudget()));
            cell.setCellStyle(dstyled);
        } else {
            cell.setCellValue("");
        }
        cell = row.createCell(8);
        cell.setCellValue(Null.convert(buds.getBudget() - buds.getCost()));
        cell.setCellStyle(dstyle);

    }

    public static void exportExcel(HSSFWorkbook demoWorkBook, HSSFSheet demoSheet) {
        try {
            String strfileName = "book.xls";
            FileOutputStream fos = null;
            //导出
            demoSheet.setGridsPrinted(true);//demoSheet
            HSSFFooter footer = demoSheet.getFooter();
            footer.setRight("Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages());
            // 获得JSF上下文环境
            FacesContext context = FacesContext.getCurrentInstance();
            // 获得ServletContext对象
            ServletContext servletContext = (ServletContext) context.getExternalContext().getContext();
            // 取得文件的绝对路径
            String excelName = servletContext.getRealPath("/upload") + "/" + strfileName;//strfileName
            File exportFile = new File(excelName);
            fos = new FileOutputStream(exportFile);//fos
            demoWorkBook.write(fos);//demoWorkBook
            fos.close();

            FacesContext ctx = FacesContext.getCurrentInstance();
            ctx.responseComplete();
            HttpServletResponse httpServletResponse = (HttpServletResponse) ctx.getExternalContext().getResponse();
            ServletOutputStream servletOutputStream = httpServletResponse.getOutputStream();
            httpServletResponse.setHeader("Content-disposition", "attachment; filename=" + strfileName);//strfileName
            httpServletResponse.setContentLength((int) exportFile.length());//exportFile
            httpServletResponse.setContentType("application/x-download");
            //httpServletResponse.setContentType("application/vnd.ms-excel");
            byte[] b = new byte[1024];
            int i = 0;
            FileInputStream fis = new java.io.FileInputStream(exportFile);
            while ((i = fis.read(b)) > 0) {
                servletOutputStream.write(b, 0, i);
            }
            servletOutputStream.flush();
            ctx.responseComplete();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public List<SelectItem> getYearli() {
        return yearli;
    }

    public void setYearli(List<SelectItem> yearli) {
        this.yearli = yearli;
    }

    public List<SelectItem> getMonthli() {
        return monthli;
    }

    public void setMonthli(List<SelectItem> monthli) {
        this.monthli = monthli;
    }

    public Department getCurrentDept() {
        return currentDept;
    }

    public void setCurrentDept(Department currentDept) {
        this.currentDept = currentDept;
    }

    public Integer getIyear() {
        return iyear;
    }

    public void setIyear(Integer iyear) {
        this.iyear = iyear;
    }

    public Integer getImonth() {
        return imonth;
    }

    public void setImonth(Integer imonth) {
        this.imonth = imonth;
    }

    public Integer getMonth_begin() {
        return month_begin;
    }

    public void setMonth_begin(Integer month_begin) {
        this.month_begin = month_begin;
    }

    public Integer getMonth_end() {
        return month_end;
    }

    public void setMonth_end(Integer month_end) {
        this.month_end = month_end;
    }

    public List<SelectItem> getMonthli_end() {
        return monthli_end;
    }

    public void setMonthli_end(List<SelectItem> monthli_end) {
        this.monthli_end = monthli_end;
    }

    public List<BudgetStats> getDeptStatsli() {
        return deptStatsli;
    }

    public void setDeptStatsli(List<BudgetStats> deptStatsli) {
        this.deptStatsli = deptStatsli;
    }

    public List<BudgetStats> getAllStatsli() {
        return allStatsli;
    }

    public void setAllStatsli(List<BudgetStats> allStatsli) {
        this.allStatsli = allStatsli;
    }

    public List<BudgetStats> getAllStatsByFunli() {
        return allStatsByFunli;
    }

    public void setAllStatsByFunli(List<BudgetStats> allStatsByFunli) {
        this.allStatsByFunli = allStatsByFunli;
    }

    public List<BudgetStats> getBudgetStatsli() {
        return budgetStatsli;
    }

    public void setBudgetStatsli(List<BudgetStats> budgetStatsli) {
        this.budgetStatsli = budgetStatsli;
    }

    public List<BudgetStats> getPubCostStatsli() {
        return pubCostStatsli;
    }

    public void setPubCostStatsli(List<BudgetStats> pubCostStatsli) {
        this.pubCostStatsli = pubCostStatsli;
    }

    public List<Pzxx> getPzxxli() {
        return pzxxli;
    }

    public void setPzxxli(List<Pzxx> pzxxli) {
        this.pzxxli = pzxxli;
    }
}
